import requests
import pymysql
import lxml.html
import re
import time

etree = lxml.html.etree

#一次爬完用户数据基本信息

class ZhiCityUser:

    #初始化函数
    def __init__(self):
        self.header = {
            'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.111 Safari/537.36',
            'Content-type': 'text/html',
            'charset': 'utf-8'}
        self.url = "http://www.taskcity.com/users"

    #获取文件信息
    def get_project(self):

        conn = pymysql.connect(host='192.168.187.130', user='root', passwd='123456', db='homework')
        cur = conn.cursor()

        for i in range(1,2787):

            print("i="+str(i))

            response = requests.get(self.url,params={"page":str(i)},headers = self.header)

            response.encoding="utf-8"

            if response.status_code == 200:
                html = etree.HTML(response.text)
                for j in range(1,31):

                    #依次获取用户信息
                    try:
                        els = html.xpath("/html/body/div[1]/div/div/div[1]/div[" + str(j) + "]/div[2]/div[1]/h4/a/@href")
                        link = str(els)[2:-2]
                        id = re.findall("/(.*?)-", str(els)[3:])[0]
                        #print("id = " + str(id))
                    except:
                        id = 0
                        print("error:id")
                        continue

                    try:
                        namel = html.xpath(
                            "/html/body/div[1]/div/div/div[1]/div[" + str(j) + "]/div[2]/div[1]/h4/a/text()")
                        name = str(namel[0])
                        #print("name:" + name)
                    except:
                        name = ""
                        print("error:name")

                    try:
                        companyl = html.xpath(
                            "/html/body/div[1]/div/div/div[1]/div[" + str(j) + "]/div[2]/div[1]/h4/small/text()")
                        company = str(companyl[0])
                        #print("company:" + company)
                    except:
                        company = name

                    #print("company:"+company)

                    els = html.xpath("/html/body/div[1]/div/div/div[1]/div[" + str(j) + "]/div[2]/div[2]/p/text()")

                    lis = re.findall("\d+", str(els))

                    try:
                        #print("id:"+id+":"+els[0])
                        goodratel = re.findall("\d+", els[0])
                        goodrate = goodratel[0]
                        #print("\ngoodrate:"+str(goodrate))
                    except:
                        goodrate = 0;
                        print("no rate!")

                    try:
                        doulis = html.xpath("/html/body/div[1]/div/div/div[1]/div["+str(j)+"]/div[2]/div[2]/p//a/text()")

                        #for ll in doulis:
                        #    print("doulis:"+ll)
                        if len(doulis) == 2:
                            pronum = re.findall("\d+",doulis[0])[0]
                            exnum = re.findall("\d+",doulis[1])[0]
                        elif len(doulis) == 1:
                            pronum = 0
                            exnum = re.findall("\d+",doulis[0])[0]
                        else:
                            pronum = 0
                            exnum = 0
                    except:
                        print("error!")
                        pronum = 0
                        exnum = 0

                    #print("pronum:"+str(pronum))
                    #print("exnum:"+str(exnum))

                    level = lis[len(lis)-2]
                    #print("level:"+str(level))

                    value = lis[len(lis)-1]
                    #print("value:"+str(value))

                    try:
                        testsql = "select count(uid) from user where uid = %s limit 1" % (id)
                        cur.execute(testsql)
                        result = cur.fetchone()

                        if result[0] > 0:
                            sql = "update user set uname = '%s' , company = '%s' , goodrate = %s , pronum = %s , exnum = %s , lv = %s , income = %s where uid = %s" % (
                                name, company, goodrate, pronum, exnum, level, value, id)
                        else:
                            sql = "insert into user(uid,uname,company,goodrate,pronum,exnum,lv,income) values (%s,'%s','%s',%s,%s,%s,%s,%s)" % (
                                id,name,company,goodrate,pronum,exnum,level,value)

                        print(sql)
                        cur.execute(sql)
                        conn.commit()
                    except:
                        print("sql error!")

                    #接下来爬标签
                    detailURL = "http://www.taskcity.com" + link
                    response = requests.get(detailURL,params={},headers = self.header)

                    response.encoding="utf-8"

                    if response.status_code == 200 :
                        detailHTML = etree.HTML(response.text)
                        skillist = detailHTML.xpath('/html/body//span[@class="tag"]/a/text()')
                        #print("skillist:"+str(skillist))
                        for ll in skillist:
                            try:
                                testsql = "select count(uid) from uskill where uid = %s and skillname = '%s' limit 1" % (id,ll)
                                #print(testsql)
                                cur.execute(testsql)
                                result = cur.fetchone()
                                if result[0] == 0:
                                    sql = "insert into uskill(uid,skillname) values(%s,'%s')" % (id,ll)
                                    #print(sql)
                                    cur.execute(sql)
                                    conn.commit()
                            except:
                                print("skill error")

                        typelist = detailHTML.xpath('/html/body/div[2]/div//span[@class="tag"]/../following-sibling::div[1]//span[@style="color:black;"]/text()')
                        #following-sibling::td[1]
                        #print("typelist:"+str(typelist))
                        #<span style="color:#a7a7a7;margin-right:10px;">服务领域</span>
                        #/html/body/div[2]/div/div[5]/span[2]
                        #/html/body/div[2]/div/div[4]/span[2]

                        for ll in typelist:
                            typename = ll.replace(" ","")
                            #print("typename:"+typename)
                            try:
                                testsql = "select count(uid) from utype where uid = %s and typename = '%s' limit 1" % (id,typename)
                                print(testsql)
                                cur.execute(testsql)
                                result = cur.fetchone()
                                if result[0] == 0 and "市" not in typename:
                                    sql = "insert into utype(uid,typename) values(%s,'%s')" % (id,typename)
                                    print(sql)
                                    cur.execute(sql)
                                    conn.commit()
                            except:
                                print("type error!")

            time.sleep(30)


        #关掉，关掉，数据库链接一定要记得关掉
        cur.close()
        conn.close()

if __name__ in "__main__":
    cls = ZhiCityUser()
    cls.get_project()
